Loan Report Builder
Standard customizable loan report using parameters for output to PDF or Excel. The Loan Report Builder may be used to create custom reports tailored to your specific needs.
| Field | Description | ||||||
|---|---|---|---|---|---|---|---|
| Selections | |||||||
| Contact Number | Contact number for which to run the report. When populated, Contact Number Starting and Contact Number Ending are disabled. | ||||||
| Contact Number Starting | Starting number of the range of contacts for which to run the report. When populated, Contact Number is disabled. | ||||||
| Contact Number Ending | Ending number of the range of contacts for which to run the report. When populated, Contact Number is disabled. | ||||||
| Contact Entity | The contact's entity type. | ||||||
| Contact Portfolio | The contact portfolio or contact type. | ||||||
| Loan Number | Loan number for which to run the report. When populated, Loan Number Starting and Loan Number Ending are disabled. | ||||||
| Loan Number Starting | Starting number of the range of loans for which to run the report. When populated, Loan Number is disabled. | ||||||
| Loan Number Ending | Ending number of the range of loans for which to run the report. When populated, Loan Number is disabled. | ||||||
| Loan Entity | The entity of the contact associated with the loan at the time the loan was created. | ||||||
| Loan Portfolio | The loan portfolio associated with the loan. | ||||||
| Loan Type | Type of loan. | ||||||
| Loan Status | The status of the loan. | ||||||
| Status Code |
The status code assigned to the loan.
|
||||||
| Loan Group | Loan group associated with the loan. | ||||||
| Loan Class | Loan class assigned to the loan. Any match in Class 1 or Class 2 will be included in the report. | ||||||
| Loan Officer | The officer assigned to the loan. | ||||||
| Collection Officer | Collection officer assigned to the contact. | ||||||
| Risk Rating | Risk rating of the loan. | ||||||
| Branch | Records belonging to the selected branch will be included in the report. | ||||||
| Dealers | Contacts with entity type of organization. | ||||||
| Commitments | Loans with loan type of commitment. | ||||||
| Where Clause | SQL WHERE clause for additional filtering by rows. See the Where Clause section below. |
||||||
| Trial Date | Loans with this trial date will be included in the report. | ||||||
| Balances To Use | The type of balance to use from the Trial Date. | ||||||
| Starting Date | Starting date of the range of dates to include in the report. If not specified, all dates up to the Ending Date are included in the report. | ||||||
| Ending Date | Ending date of the range of dates to include in the report. If not specified, all dates from the Starting Date forward are included in the report. | ||||||
| Date To Use | The date field designation for Starting Date and Ending Date. | ||||||
| Report Title | The title is displayed across the top of the page on a PDF output or in cell A1 on an Excel output. | ||||||
| Paper Size | PDF Paper size to use for the report. | ||||||
| Orientation | PDF Sets the orientation of output as portrait or landscape. | ||||||
| Lines Per Inch | PDF Number of lines of text to print within a vertical space of one inch. | ||||||
| Font Size | PDF Font size to use for the output. | ||||||
| Column 1 ~ 20 | Select which data field to include in the report for the corresponding column. See the list of available fields. |
||||||
| Fit Columns To Page | PDF When selected, each column width will be reduced, truncating the data displayed, to fit all columns within the width of the page. | ||||||
| Number Of Detail Lines | PDF The number of lines of text to use to display information on each report entry. | ||||||
| Number Of Heading Lines | PDF Maximum number of lines of text to use to display the heading. | ||||||
| Include Group Headings | PDF When selected, group headings as defined in the Grouping settings, are included in the report. | ||||||
| Show Totals Only | When selected, only the totals lines are included in the report. | ||||||
| Show 2 Places | When selected, applicable data will be truncated to display two decimal places. | ||||||
| Grouping | |||||||
| Group By 1/Page Break | Output is grouped by the selected field. When the checkbox is selected, new groupings start on a new page. | ||||||
| Group By 2/Page Break | Output is grouped by the selected field within the Group By 1 grouping. When the checkbox is selected, new groupings start on a new page. | ||||||
| Sorting | |||||||
| Sort By | Output of reports will be sorted in ascending order of the selected field. | ||||||
| Sort By 2 | Secondary sort order. Output of reports will be sorted in ascending order of the selected field. | ||||||
| Output | |||||||
| Report Output | Output type or format of the generated report. | ||||||
| Fields To Include | Excel CSV Click  to choose which fields to include in the Excel output. |
||||||
Ignore
Include
ExcludeOutput
| Field | Description |
|---|---|
| Column 1 - 20 | Data from the Column 1 - 20 selections are shown on the report. |
Where Clause
The Where Clause option can be used to filter the selection by additional rows.
| Table Name | Connected From | Connected To | Alias Of | Purpose |
|---|---|---|---|---|
| loanacct | Loan | |||
| cif | loanacct.cifno | cif.cifno | Borrower | |
| cif_port_codes | cif.portfolio_code_id | cif_port_codes.portfolio_code_id | Borrower Contact Type | |
| nlsusers_1 | cif.officer_number | nlsusers_1.userno | nslusers | Borrower Officer |
| loan_port_codes | loanacct.portfolio_code_id | loan_port_codes.portfolio_code_id | Loan Portfolio | |
| loan_group | loanacct.loan_group_no | loan_group.loan_group_no | Loan Group | |
| loan_class_1 | loanacct.loan_class1_no | loan_class_1.codenum | loan_class | Loan Class 1 |
| loan_class_2 | loanacct.loan_class2_no | loan_class_2.codenum | loan_class | Loan Class 2 |
| nlsusers_2 | loanacct.loan_officer_no | nlsusers_2.userno | nlsusers | Loan Officer |
| nlsusers_3 | loanacct.collection_officer_no | nlsusers_3.userno | nlsusers | Collection Officer |
| risk_rating_code | loanacct.risk_rating_no | risk_rating_code.risk_rating_no | Risk Rating | |
| loanacct_setup | loanacct.acctrefno | loanacct_setup.acctrefno | Loan Setup | |
| loanacct_payment | loanacct.acctrefno | loanacct_payment.acctrefno | Loan Payment Setup | |
| loanacct_statistics | loanacct.acctrefno |
loanacct_statistics.acctrefno And loanacct_statistics.master_record = 0 And loanacct_statistics.year_number = 0 |
Loan Statistics | |
| loanacct_detail | loanacct.acctrefno | loanacct_detail | Detail Detail Tab | |
| loanacct_detail_2 | loanacct.acctrefno | loanacct_detail_2 | Detail Detail Tab 2 | |
| cif_detail | loanacct.cifno | cif_detail | Borrower Detail | |
| cif_demographics | loanacct.cifno | cif_demographics | Borrower Demographics | |
| cif_financials | loanacct.cifno | cif_financials | Borrower Financials | |
| cif_2 | loanacct.dealer_cifno | cif_2.cifno | cif | Loan’s Dealer Contact |
| loanacct_4 | loanacct.master_acctrefno | loanacct_4.acctrefno | loanacct | Loan’s Commitment |
| loanacct_6 | loanacct.pool_acctrefno | loanacct_6.acctrefno | loanacct | Loan’s Pool |
Example
To match any loans with one or more late payments:loanacct_statistics.days_late_10 > 0Example
To match any loans where the borrower’s address is in the state of California:cif.state = 'CA'Example
To match any loans where the borrower’s contact type is Borrower:cif_port_codes.portfolio_code = 'BORROWERS'Example
To match any loans where the borrower’s officer is Administrator:nlsusers_1.username = 'Administrator'Example
To match any where the borrower’s UDF01 field in the Detail tab is 2005/01/01:cif_detail.userdef01 > '2005/01/01'Example
To match any loans where the borrower’s UDF01 field in the Demographics tab is Urban:cif_demographics.userdef01 = 'URBAN'Example
Where clauses may be combined using AND/OR conditions to create complex queries. Use parenthesis to group clauses in the order of evaluation.To match any loans where the borrower’s address is in the state of California AND the loan’s current principal is less than 2,000 AND the original loan amount is greater than 10,000 OR any loan’s borrower is in California AND the original loan amount is less than 5,000:
cif.state = 'CA' AND ((loanacct.current_principal_balance < 2000 AND loanacct.original_note_amount > 10000) OR (loanacct.original_note_amount < 5000))The generated Excel document can also be sorted and filtered in Excel to further refine the report.
